﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for TourDAO
/// </summary>
public class TourDAO:DBAccess
{
	 
    #region sql Commands   
    string SQL_SELECT_ALL = "select * from Tour";
    string SQL_SELECT_BY_ID = "select * from Tour where ID=@id";
    string SQL_SELECT_TOUR_COUNT = "select count(*) from Tour where State=1";
    string SQL_SELECT_RANDOM = "select top (@quantity) ID, [Name],Region, During, Description,ImageURL, Price, Discount from Tour order by newid()";
    string SQL_INSERT = "insert into Tour(Name,LocationID,Region, During, Price, Discount, Description,ImageURL,Rank, Article,IsRecommended,Tag, ModifiedDate,ModifiedBy,State) values(@Name,@LocationID,@Region, @During, @Price, @Discount, @Description,@ImageURL,@Rank,@Article,@IsRecommended,@Tag, @ModifiedDate,@ModifiedBy,@State)";
    string SQL_UPDATE = "update Tour set Name = @Name, LocationID = @LocationID, Region = @Region, During = @During, Price = @Price, Discount = @Discount,Description= @Description,ImageURL=@ImageURL ,Rank=@Rank,Article=@Article,IsRecommended=@IsRecommended,Tag=@Tag ,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy,State=@State where ID = @id";
    string SQL_DELETE = "delete from Tour where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public TourDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get tour by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Tour GetTour(int id)
    {
        Tour tour = new Tour();
        try
        {

            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tour.Id = dr.GetInt32(0);
                    tour.Name = dr.GetString(1);
                    tour.Location.Id = dr.GetInt32(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.Article = dr.GetString(6);
                    tour.Tag = dr.GetString(7);
                    tour.Price = dr.GetDecimal(8);
                    tour.Discount = dr.GetDecimal(9);
                    tour.IsRecommended = dr.GetBoolean(10);
                    tour.Rank = dr.GetInt32(11);
                    tour.State = dr.GetBoolean(12);
                    tour.ModifiedDate = dr.GetDateTime(13);
                    tour.ModifiedBy = dr.GetInt32(14);
                    tour.ImageURL = dr.GetString(15);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return tour;
    }
    /// <summary>
    /// Get all tours 
    /// </summary>
    /// <returns></returns>
   public IList<Tour> GetAllTours()
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(0);
                    tour.Name = dr.GetString(1);
                    tour.Location.Id = dr.GetInt32(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.Article = dr.GetString(6);
                    tour.Tag = dr.GetString(7);
                    tour.Price = dr.GetDecimal(8);
                    tour.Discount = dr.GetDecimal(9);
                    tour.IsRecommended = dr.GetBoolean(10);
                    tour.Rank = dr.GetInt32(11);
                    tour.State = dr.GetBoolean(12);
                    tour.ModifiedDate = dr.GetDateTime(13);
                    tour.ModifiedBy = dr.GetInt32(14);
                    tour.ImageURL = dr.GetString(15);
                    list.Add(tour);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
   /// <summary>
   /// Insert new tour into database
   /// </summary>
   /// <param name="tour"></param>
   /// <returns></returns>
   public int Insert(Tour tour)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_INSERT, conn);
           command.Parameters.AddWithValue("@Name", tour.Name);          
           command.Parameters.AddWithValue("@LocationID", tour.Location.Id);
           command.Parameters.AddWithValue("@Region", tour.Region);
           command.Parameters.AddWithValue("@During", tour.During);
           command.Parameters.AddWithValue("@Price", tour.Price);
           command.Parameters.AddWithValue("@Discount", tour.Discount);
           command.Parameters.AddWithValue("@Description", tour.Description);
           command.Parameters.AddWithValue("@ImageURL", tour.ImageURL);
           command.Parameters.AddWithValue("@Rank", tour.Rank);
           command.Parameters.AddWithValue("@Article", tour.Article);
           command.Parameters.AddWithValue("@IsRecommended", tour.IsRecommended);
           command.Parameters.AddWithValue("@Tag", tour.Tag);
           command.Parameters.AddWithValue("@ModifiedDate", tour.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", tour.ModifiedBy);
           command.Parameters.AddWithValue("@State", tour.State);
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               command.CommandText = "SELECT @@IDENTITY from Tour";
               tour.Id = int.Parse(command.ExecuteScalar().ToString());
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Update Person
   /// </summary>
   /// <param name="tour"></param>
   /// <returns></returns>
   public int Update(Tour tour)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
           command.Parameters.AddWithValue("@Name", tour.Name);    
           command.Parameters.AddWithValue("@LocationID", tour.Location.Id);
           command.Parameters.AddWithValue("@Region", tour.Region);
           command.Parameters.AddWithValue("@During", tour.During);
           command.Parameters.AddWithValue("@Price", tour.Price);
           command.Parameters.AddWithValue("@Discount", tour.Discount);
           command.Parameters.AddWithValue("@Description", tour.Description);
           command.Parameters.AddWithValue("@ImageURL", tour.ImageURL);
           command.Parameters.AddWithValue("@Rank", tour.Rank);
           command.Parameters.AddWithValue("@Article", tour.Article);
           command.Parameters.AddWithValue("@IsRecommended", tour.IsRecommended);
           command.Parameters.AddWithValue("@Tag", tour.Tag);
           command.Parameters.AddWithValue("@ModifiedDate", tour.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", tour.ModifiedBy);
           command.Parameters.AddWithValue("@State", tour.State);
           command.Parameters.AddWithValue("@id", tour.Id);  
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Remove tour out of database
   /// </summary>
   /// <param name="tour"></param>
   /// <returns></returns>
   
    public int Delete(int Id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    /// <summary>
    /// Get list of tours with paging
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="state">0:published, 1:unpublished, others number(2-255) means both of them</param>
    /// <returns></returns>
    public IList<Tour> GetTours(int currentPage, int pageSize, ref int total, byte state)
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectTours", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt); 
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(1);
                    tour.Name = dr.GetString(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.ImageURL = dr.GetString(6);
                    tour.Price = dr.GetDecimal(7);
                    tour.Discount = dr.GetDecimal(8);
                    tour.State = dr.GetBoolean(9);
                    list.Add(tour);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public IList<Tour> GetToursByRegion(int currentPage, int pageSize, ref int total,string region, byte state)
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectToursByRegion", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@region", SqlDbType.NVarChar);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@region"].Value = region;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(1);
                    tour.Name = dr.GetString(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.ImageURL = dr.GetString(6);
                    tour.Price = dr.GetDecimal(7);
                    tour.Discount = dr.GetDecimal(8);
                    tour.State = dr.GetBoolean(9);
                    list.Add(tour);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Get list of tour with paging and condition (search by name, description, tag, article)
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="keyword">text to search</param>
    /// <returns></returns>
    public IList<Tour> GetToursExt(int currentPage, int pageSize, ref int total, string keyword,  byte state)
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectToursExt", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(1);
                    tour.Name = dr.GetString(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.ImageURL = dr.GetString(6);
                    tour.Price = dr.GetDecimal(7);
                    tour.Discount = dr.GetDecimal(8);
                    tour.State = dr.GetBoolean(9);
                    list.Add(tour);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    /// <summary>
    /// Get list of tour by price range, text, rank. (all location)
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="keyword"></param>
    /// <param name="lowPrice"></param>
    /// <param name="highPrice"></param>
    /// <param name="orderByRank"></param>
    /// <param name="state"></param>
    /// <returns></returns>
    public IList<Tour> GetToursAdv(int currentPage, int pageSize, ref int total, string keyword, decimal lowPrice, decimal highPrice, bool orderByRank, byte state)
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectToursAdv", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@lowPrice", SqlDbType.Money);
            command.Parameters.Add("@highPrice", SqlDbType.Money);
            command.Parameters.Add("@orderByRank", SqlDbType.Bit);        
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@lowPrice"].Value = lowPrice;
            command.Parameters["@highPrice"].Value = highPrice;
            command.Parameters["@orderByRank"].Value = orderByRank;           
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(1);
                    tour.Name = dr.GetString(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.ImageURL = dr.GetString(6);
                    tour.Price = dr.GetDecimal(7);
                    tour.Discount = dr.GetDecimal(8);
                    tour.State = dr.GetBoolean(9);
                    list.Add(tour);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Get list of tours by price range, rank, name, location
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="keyword">text to search</param>
    /// <param name="state">state: published or not</param>
    /// <param name="state">location: published or not</param>
    /// <returns></returns>
    public IList<Tour> GetToursAdvByLoc(int currentPage, int pageSize, ref int total, string keyword,decimal lowPrice, decimal highPrice,bool orderByRank,int locationId, byte state)
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectToursAdvByLoc", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@lowPrice", SqlDbType.Money);
            command.Parameters.Add("@highPrice", SqlDbType.Money);
            command.Parameters.Add("@orderByRank", SqlDbType.Bit);
            command.Parameters.Add("@locationId", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@lowPrice"].Value = lowPrice;
            command.Parameters["@highPrice"].Value = highPrice;
            command.Parameters["@orderByRank"].Value = orderByRank;
            command.Parameters["@locationId"].Value = locationId;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(1);
                    tour.Name = dr.GetString(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.ImageURL = dr.GetString(6);
                    tour.Price = dr.GetDecimal(7);
                    tour.Discount = dr.GetDecimal(8);
                    tour.State = dr.GetBoolean(9);
                    list.Add(tour);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Get list of tours belong to a location
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="locationId"></param>
    /// <returns></returns>
    public IList<Tour> GetToursByLocation(int currentPage, int pageSize, ref int total, int locationId, byte state)
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectToursByLocation", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@locationId", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@locationId"].Value = locationId;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(1);
                    tour.Name = dr.GetString(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.ImageURL = dr.GetString(6);
                    tour.Price = dr.GetDecimal(7);
                    tour.Discount = dr.GetDecimal(8);
                    tour.State = dr.GetBoolean(9);
                    list.Add(tour);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    /// <summary>
    /// Get recommended tours
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="isRecommended">if it is recommended or not</param>
    /// <returns></returns>
    public IList<Tour> GetRecommendedTours(int currentPage, int pageSize, ref int total, bool isRecommended, byte state)
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectRecommendedTours", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@recommended", SqlDbType.Bit);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@recommended"].Value = isRecommended;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(1);
                    tour.Name = dr.GetString(2);
                    tour.Region = dr.GetString(3);
                    tour.During = dr.GetInt16(4);
                    tour.Description = dr.GetString(5);
                    tour.ImageURL = dr.GetString(6);
                    tour.Price = dr.GetDecimal(7);
                    tour.Discount = dr.GetDecimal(8);
                    tour.State = dr.GetBoolean(9);
                    list.Add(tour);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Get tour random
    /// </summary>
    /// <param name="quantity">Number of records to get</param>
    /// <returns></returns>
    public IList<Tour> GetToursInRandom(int quantity )
    {
        IList<Tour> list = new List<Tour>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_RANDOM, conn);
            command.Parameters.AddWithValue("@quantity",quantity);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Tour tour = new Tour();
                    tour.Id = dr.GetInt32(0);
                    tour.Name = dr.GetString(1);
                    tour.Region = dr.GetString(2);
                    tour.During = dr.GetInt16(3);
                    tour.Description = dr.GetString(4);
                    tour.ImageURL = dr.GetString(5);
                    tour.Price = dr.GetDecimal(6);
                    tour.Discount = dr.GetDecimal(7);                   
                    list.Add(tour);
                }
                dr.Close();               
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    /// <summary>
    /// Get number of available tours
    /// </summary>
    /// <param name="firstDate"></param>
    /// <param name="secondDate"></param>
    /// <returns></returns>
    public int GetTourCount()
    {
        int quantity = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_TOUR_COUNT, conn);
            if (Connect())
            {                
                quantity = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return quantity;

    }
    #endregion
}